package com.moleq.service;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.moleq.db.DBError;
import com.moleq.db.DatabaseHelper;
import com.moleq.db.DatabaseSchema;
import com.moleq.dialog.MessageBox;
import com.moleq.models.Departmant;
import com.moleq.models.SetFunction;

public class DepartmantService {
	private DatabaseHelper DBHelper;
	private String errorString;
	private Context mContext;
	public DepartmantService() {

	}

	public DepartmantService(Context context) {
		this.DBHelper = new DatabaseHelper(context);
		mContext = context;
	}
	
	public List<Departmant> FindAll(){
		List<Departmant> obj = new ArrayList<Departmant>();
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = DBHelper.rawQuery("select * from t_department_setting order by F03",null);
		if(cursor == null){
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return obj;
		}
		
		while(cursor.moveToNext()){
			boolean dtax1= false,dtax2= false,dtax3= false,dtax4= false,dtax5= false,dtax6= false,dfoodstamp = false,bscalable = false,isnodiscount = false;
			int dep_no = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_NO));
			String dep_name = cursor.getString(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_NAME));
			String food_stamp = cursor.getString(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_FOOD_STAMP));
			int tax1 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_TAX1));
			int tax2 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_TAX2));
			int tax3 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_TAX3));
			int tax4 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_TAX4));
			int tax5 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_TAX5));
			int tax6 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_TAX6));
			int scalable = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.SCALABLE));
			String wic = cursor.getString(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.WIC));
			String no_discount = cursor.getString(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.NO_DISCOUNT));
			String cvv = cursor.getString(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.CVV));
			int group = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_GROUP));
			int age = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.ds_AGE_VER));
			String updateTime = cursor.getString(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.UPDATE_DATETIME));
			String createTime = cursor.getString(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.CREATE_DATETIME));
			
			if(food_stamp.equals("1")) dfoodstamp = true;
			if(tax1 > 0) dtax1 = true;
			if(tax2 > 0) dtax2 = true;
			if(tax3 > 0) dtax3 = true;
			if(tax4 > 0) dtax4 = true;
			if(tax5 > 0) dtax5 = true;
			if(tax6 > 0) dtax6 = true;
			if(scalable > 0) bscalable = true;
			if(no_discount.equals("1"))isnodiscount = true;
			
			obj.add(new Departmant(dep_no,dep_name,dfoodstamp,dtax1,dtax2,dtax3,dtax4,dtax5,dtax6,bscalable,wic,isnodiscount,cvv,group,age,createTime,updateTime));
		}
		cursor.close();
		return obj;
	}
	
	public Departmant Find(double dep){
		
		Departmant obj = null;
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = DBHelper.rawQuery("select * from t_department_setting where F03 = "+dep,null);
		
		if(cursor == null){
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return null;
		}
		while(cursor.moveToNext()){
			boolean dtax1= false,dtax2= false,dtax3= false,dtax4= false,dtax5= false,dtax6= false,dfoodstamp = false,bscalable = false,isnodiscount = false;
			int dep_no = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_NO));
			String dep_name = cursor.getString(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_NAME));
			String food_stamp = cursor.getString(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_FOOD_STAMP));
			int tax1 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_TAX1));
			int tax2 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_TAX2));
			int tax3 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_TAX3));
			int tax4 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_TAX4));
			int tax5 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_TAX5));
			int tax6 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_TAX6));
			int scalable = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.SCALABLE));
			String wic = cursor.getString(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.WIC));
			String no_discount = cursor.getString(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.NO_DISCOUNT));
			String cvv = cursor.getString(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.CVV));
			int group = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_GROUP));
			int age = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.ds_AGE_VER));
			String updateTime = cursor.getString(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.UPDATE_DATETIME));
			String createTime = cursor.getString(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.CREATE_DATETIME));
			
			if(food_stamp.equals("1")) dfoodstamp = true;
			if(tax1 > 0) dtax1 = true;
			if(tax2 > 0) dtax2 = true;
			if(tax3 > 0) dtax3 = true;
			if(tax4 > 0) dtax4 = true;
			if(tax5 > 0) dtax5 = true;
			if(tax6 > 0) dtax6 = true;
			if(scalable > 0) bscalable = true;
			if(no_discount.equals("1"))isnodiscount = true;
			
			obj = new Departmant(dep_no,dep_name,dfoodstamp,dtax1,dtax2,dtax3,dtax4,dtax5,dtax6,bscalable,wic,isnodiscount,cvv,group,age,createTime,updateTime);
					
		}
		cursor.close();
		return obj;
	
	}
	
	public Departmant FindDetail(String dep){
		
		Departmant obj = null;
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		String sql = "select F03,dep_name,F81,F79,F96,F97,F98,F82,F178,F150,F924,F263,ds_age_ver,F99,F100,as_desc from t_department_setting " +
				" LEFT JOIN t_age_setting ON ds_age_ver = as_no where F03 = "+ dep;
		Cursor cursor = DBHelper.rawQuery(sql,null);
		if(cursor == null){
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return null;
		}
		
		if(cursor.moveToNext()){
			boolean dtax1= false,dtax2= false,dtax3= false,dtax4= false,dtax5= false,dtax6= false,dfoodstamp = false,bscalable = false,isnodiscount = false;
			int dep_no = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_NO));
			String dep_name = cursor.getString(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_NAME));
			String food_stamp = cursor.getString(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_FOOD_STAMP));
			int tax1 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_TAX1));
			int tax2 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_TAX2));
			int tax3 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_TAX3));
			int tax4 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_TAX4));
			int tax5 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_TAX5));
			int tax6 = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_TAX6));
			int scalable = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.SCALABLE));
			String wic = cursor.getString(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.WIC));
			String no_discount = cursor.getString(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.NO_DISCOUNT));
			String cvv = cursor.getString(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.CVV));
			int group = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_GROUP));
			int age = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.ds_AGE_VER));
			String age_desc = cursor.getString(cursor.getColumnIndex(DatabaseSchema.AgeSetting.AS_DESC));
			
			if(food_stamp.equals("1")) dfoodstamp = true;
			if(tax1 > 0) dtax1 = true;
			if(tax2 > 0) dtax2 = true;
			if(tax3 > 0) dtax3 = true;
			if(tax4 > 0) dtax4 = true;
			if(tax5 > 0) dtax5 = true;
			if(tax6 > 0) dtax6 = true;
			if(scalable > 0) bscalable = true;
			if(no_discount.equals("1"))isnodiscount = true;
			
			obj = new Departmant(dep_no,dep_name,dfoodstamp,dtax1,dtax2,dtax3,dtax4,dtax5,dtax6,bscalable,wic,isnodiscount,cvv,group,age,age_desc);
					
		}
		cursor.close();
		return obj;
	
	}
	
	public boolean isHaveDept(String dep){
		boolean check = false;
		
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		
		Cursor cursor = DBHelper.rawQuery("select F03 from t_department_setting where F03 = "+dep,null);
		
		if(cursor == null){
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return check;
		}

		if(cursor.moveToNext()){
			check = true;
		}
		cursor.close();
		return check;
		
	}
	
	public boolean insert(Departmant dep){
		boolean check = true;
		String sql,taxable, food_stamp, tax2, tax3, tax4, tax5, tax6, scalable , discount,ls_group;
		int li_age;
		taxable = dep.getTax1()?"1":"0";
		food_stamp = dep.getFood_stamp()?"1":"0";
		tax2 = dep.getTax2()?"1":"0";
		tax3 = dep.getTax3()?"1":"0";
		tax4 = dep.getTax4()?"1":"0";
		tax5 = dep.getTax5()?"1":"0";
		tax6 = dep.getTax6()?"1":"0";
		scalable = dep.getScale_weight()?"1":"0";
		discount = dep.getNo_discnt()?"1":"0";
		li_age = dep.getAge()==0?0:dep.getAge();
	    ls_group = 	dep.getGroup_no() == 0?null:String.valueOf(dep.getGroup_no());	
	    
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		
		sql = "insert into t_department_setting values";
		sql += "(" + dep.getDep_no() + ",'" + dep.getDep_name() + "','" + taxable + "','"
				+ food_stamp + "',strftime('%Y%m%d%H%M%S','now'),strftime('%Y%m%d%H%M%S','now'),'"
				+ tax2 + "','" + tax3 + "','" + tax4 + "','" + scalable
				+ "','" + dep.getWic() + "','" + discount + "','" + dep.getCvv() + "',"
				+ ls_group + "," + li_age + ",'" + tax5 + "','" + tax6
				+ "')";		
		errorString = DBHelper.execSQL(sql);

		if (errorString.length() > 0) {
			check = false;
		} 
		return check;
//		db.execSQL(
//				"insert into t_department_setting values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
//				new Object[]{dep.getDep_no(),dep.getDep_name(),taxable,
//				food_stamp,"strftime('%Y%m%d%H%M%S','now')","strftime('%Y%m%d%H%M%S','now')",tax2,tax3,tax4,scalable,dep.getWic(),discount,dep.getCvv(),
//				ls_group,li_age,tax5,tax6});
	}
	
	public boolean update(Departmant dep){
		boolean check = true;
		String taxable, food_stamp, tax2, tax3, tax4, tax5, tax6, scalable , discount,ls_group; 
		int li_age;
		String sql = "";
		taxable = dep.getTax1()?"1":"0";
		food_stamp = dep.getFood_stamp()?"1":"0";
		tax2 = dep.getTax2()?"1":"0";
		tax3 = dep.getTax3()?"1":"0";
		tax4 = dep.getTax4()?"1":"0";
		tax5 = dep.getTax5()?"1":"0";
		tax6 = dep.getTax6()?"1":"0";
		scalable = dep.getScale_weight()?"1":"0";
		discount = dep.getNo_discnt()?"1":"0";
		li_age = dep.getAge()==0?0:dep.getAge();
	    ls_group = dep.getGroup_no() == 0?null:String.valueOf(dep.getGroup_no());	
	    
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		sql = "update t_department_setting set ";
		sql += "F03 = " + dep.getDep_no() + "," + "dep_name  = '" +dep.getDep_name()
				+ "'," + "F81  = '" + taxable + "'," + "F79  = '"
				+ food_stamp + "'," + "update_datetime = strftime('%Y%m%d%H%M%S','now')," 
				+ "F96 = '" + tax2 + "'," + "F97 = '" + tax3 + "',"
				+ "F98 = '" + tax4 + "'," + "F82 = '" + scalable + "',"
				+ "F178 = '" + dep.getWic() + "'," + "F150 = '" + discount + "',"
				+ "F924 = '" + dep.getCvv() + "'," + "F263 = " + ls_group + ","
				+ "F99 = " + tax5 + "," + "F100 = " + tax6 + ","
				+ "ds_age_ver = " + li_age ;

		sql += " where F03 = '" + dep.getDep_no() + "'";
		
		DBHelper.execSQL(sql);
		errorString = DBHelper.execSQL(sql);

		if (errorString.length() > 0) {
			check = false;
		} 
		return check;
//		db.execSQL("update t_department_setting set dep_name = ?,F81 = ?,F79 = ?,update_datetime = ?,F96 = ?,F97 = ?,F98 = ?,F82 = ?,F178 = ?,F150 = ?" +
//				",F924 = ?,F263 = ?,F99 = ?,F100 = ?, ds_age_ver = ? where F03 = ?",
//				new Object[]{dep.getDep_name(),taxable,food_stamp,"strftime('%Y%m%d%H%M%S','now')",tax2,tax3,tax4,scalable,dep.getWic(),discount,dep.getCvv(),
//				ls_group,tax5,tax6,ls_age,dep.getDep_no()});
	}
	
	public boolean Delete(String dep_no){
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		boolean check = true;
		
		String sql = "Delete from t_department_setting where F03 = " + dep_no;
		errorString = DBHelper.execSQL(sql);

		if (errorString.length()> 0) {
			check = false;
		} 
		return check;
		
	}
	
	
	public String Getgroupbyid(String id) {
		String ls_id = "";
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		String sql = "select dg_name from t_dept_group where dg_no  = " + id;
		Cursor cursor = DBHelper.rawQuery(sql, null);
		if(cursor == null){
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return "";
		}
		if (cursor.moveToNext()) {

			ls_id = cursor.getString(0);
		}

		cursor.close();
		return ls_id;

	}
	
	public List<SetFunction> FindDepNoDesc(){
		List<SetFunction> obj = new ArrayList<SetFunction>();
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select F03,dep_name from "+ DatabaseSchema.DepartmentSetting.SCHEMA_NAME +" order by F03",null);
		if(cursor == null){
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return obj;
		}
		obj.add(0, new SetFunction("0", "Department"));
		while(cursor.moveToNext()){
			String dep_no  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_NO));
			String dep_name  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_NAME));
			
			obj.add(new SetFunction(dep_no, dep_name));
		}
		cursor.close();
		return obj;
	}
	
	public String getDepName(String no){
		String obj = null;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select dep_name from "+ DatabaseSchema.DepartmentSetting.SCHEMA_NAME +" where F03 = "+no,null);
		if(cursor == null){
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return obj;
		}
		if(cursor.moveToNext()){
			obj = cursor.getString(cursor.getColumnIndex(DatabaseSchema.DepartmentSetting.DEPARTMENT_NAME));
		}
		cursor.close();
		return obj;
	}
}
